const router = require("koa-router")();
const { getDbHelper } = require("../DbHelpers");

router.prefix("/api/db/compare", '数据库比较服务');

/**
 * 比较数据库的区别。
 */
router.get("/:dbIndex1/:dbName1/to/:dbIndex2/:dbName2",
  async (ctx, next) => {
    let dbIndex1 = ctx.params.dbIndex1;
    let dbIndex2 = ctx.params.dbIndex2;
    let dbName1 = ctx.params.dbName1;
    let dbName2 = ctx.params.dbName2;

    let dbHelper1 = getDbHelper(dbIndex1);
    let dbHelper2 = getDbHelper(dbIndex2);

    let tables1Query = dbHelper1.tables(dbName1);
    let tables2Query = dbHelper2.tables(dbName2);
    let tables1Indexs = dbHelper1.indexs(dbName1);
    let tables2Indexs = dbHelper2.indexs(dbName2);

    let res = {};

    // 获取源库与目标库的表。
    await Promise.all([
      tables1Query,
      tables2Query,
      tables1Indexs,
      tables2Indexs
    ]).then(([tables1, tables2, indexs1, indexs2]) => {
      res.tables1 = tables1.datas.map(item => {
        return {
          name: item.table_name,
          comment: item.table_comment || ""
        };
      }).filter(t => !/\d+/.test(t.name) && t.name.indexOf('bak') == -1 && t.name.indexOf('back') == -1);

      res.tables2 = tables2.datas.map(item => {
        return {
          name: item.table_name,
          comment: item.table_comment || ""
        };
      }).filter(t => !/\d+/.test(t.name) && t.name.indexOf('bak') == -1 && t.name.indexOf('back') == -1);
      res.indexs1 = indexs1.datas;
      res.indexs2 = indexs2.datas;
    });

    let result = {
      source: {
        dbIndex: dbIndex1,
        dbName: dbName1
      },
      target: {
        dbIndex: dbIndex2,
        dbName: dbName2
      }
    };

    let ignores = ["comment"];

    // 获取需要添加的表。
    result.adds = res.tables1.getNotIncludes(res.tables2, ignores);

    // 获取需要删除的表
    result.removes = res.tables2.getNotIncludes(res.tables1, ignores);

    // 获取已存在的表
    let existTables = res.tables1.filter(item =>
      res.tables2.find(n => n.name.toLowerCase() == item.name.toLowerCase())
    );

    // 设置发生改变的表
    await setTableChangeds(result, existTables);

    // 获取需要添加的索引
    result.addIndexs = (res.indexs1 || []).getNotIncludes(res.indexs2, ["columns"]);

    // 获取需要删除的索引
    result.removeIndexs = (res.indexs2 || []).getNotIncludes(res.indexs1, ["columns"]);

    // 获取已存在的索引
    result.changedIndexs = (res.indexs1 || []).filter(item => {
      var temp = res.indexs2.find(
        n =>
          n.table_name.toLowerCase() == item.table_name.toLowerCase() &&
          (n.index_name || '').toLowerCase() == (item.index_name || '').toLowerCase()
      );

      if (temp) {
        return res.indexs2.find(
          n =>
            n.table_name.toLowerCase() == temp.table_name.toLowerCase() &&
            (n.index_name || '').toLowerCase() == (temp.index_name || '').toLowerCase() &&
            n.columns.toLowerCase() != temp.columns.toLowerCase()
        );
      }

      return false;
    });

    ctx.body = result;
  }, { desc: '获取两个数据库的差异', dbIndex1: '数据库1的服务序号或名称', dbName1: '数据库1的名称', dbIndex2: '数据库2的服务序号或名称', dbName2: '数据库1的名称' }
);

/**
 * 获取表的创建或删除语句。
 */
router.post("/ddl/build", async (ctx, next) => {
  let data = ctx.request.body;
  let ddls = [];
  let addPromises = [];

  let dbHelper = getDbHelper(data.source.dbIndex);

  if (data.removes && data.removes.length > 0) {
    data.removes.forEach(item => {
      dbHelper.type == 'mysql' ? ddls.push(`drop table \`${item.name}\`;`) : ddls.push(`drop table ${item.name};`);
    });
  }

  if (data.adds && data.adds.length > 0) {
    data.adds.forEach(element => {
      let tableName = element.name;

      addPromises.push(
        dbHelper.tableDDL(
          data.source.dbName,
          tableName
        )
      );
    });

    await Promise.all(addPromises).then(values => {
      values.forEach(rs => ddls.push(`${rs.data["Create Table"]}${dbHelper.type == 'mysql' ? ';\n' : ''}`));
    });
  }

  if (data.changeds && data.changeds.length > 0) {
    ddls.push(...(dbHelper.columnChangesDdls(data.changeds)));
  }

  ctx.body = ddls;
}, {
  desc: '根据数据库比较差异信息生成DDL语句', body: {
    removes: { type: 'array', items: { type: 'string' }, desc: '删除的表集合' },
    adds: { type: 'array', items: { type: 'string' }, desc: '添加的表集合' },
    changeds: {
      type: 'array',
      items: {
        type: 'object',
        properties: {
          table: { type: 'string', description: '表名称' },
          column_name: { type: 'string', description: '字段名称' },
          column_type: { type: 'string', description: '字段类型' },
          is_nullable: { type: 'integer', description: '字段是否可空(1：可空、其他：不可空)' },
          column_default: { type: 'string', description: '字段默认值' },
          flag:  { type: 'string', description: '标记(add：新增列、其他：修改)' },
        }
      }, desc: '发生更改的表集合'
    },
  }
});

/**
 * 设置发生变化的表。
 * @param {Object} res 比较结果
 * @param {Array} existTables 源数据库和目标数据库都存在的表集合
 * @returns {Promise} 异步对象
 */
let setTableChangeds = async function (res, existTables) {
  let getTableColumns = [];
  let tableColumns = {};

  if (existTables && existTables.length > 0) {
    let dbHelper1 = getDbHelper(res.source.dbIndex);
    let dbHelper2 = getDbHelper(res.target.dbIndex);

    existTables.forEach(element => {
      let tableName = element.name;

      getTableColumns.push(
        new Promise((resolve, reject) =>
          Promise.all([
            dbHelper1.columns(
              res.source.dbName,
              tableName
            ),
            dbHelper2.columns(
              res.target.dbName,
              tableName
            )
          ]).then(([source, target]) => {
            resolve({
              table: element.name,
              comment: element.comment,
              changeds: getChangedColumns(source.datas, target.datas)
            });
          })
        )
      );
    });

    await Promise.all(getTableColumns).then(values => {
      tableColumns = values.filter(item => item.changeds.length > 0);
    });

    res.changeds = tableColumns;
  }
};

/**
 * 获取已发生变化的列。
 * @param {Array} sColumns 源数据库
 * @param {Array} tColumns 目标数据库
 */
let getChangedColumns = function (sColumns, tColumns) {
  let changeds = [];

  (sColumns || []).forEach(column => {
    /** 全部相同 */
    let completeEqual = (tColumns || []).find(item => item.compare(column, ["column_key", "Sort"]));
    let nameEqual = (tColumns || []).find(item => {
      let rs = item.column_name.toLowerCase() == column.column_name.toLowerCase();

      if (rs) {
        column.target = item;
      }

      return rs;
    });

    if (!completeEqual) {
      if (nameEqual) {
        column.flag = "change";
      } else {
        column.flag = "add";
      }

      changeds.push(column);
    }
  });

  return changeds;
};

module.exports = router;